package com.sailfish.springbootdemo.service.db7.Impl;

import com.sailfish.springbootdemo.configs.UserHeaderHolder;
import com.sailfish.springbootdemo.service.db7.TbDynamicVplDataService;
import com.sailfish.springbootdemo.dao.db7.*;
import com.sailfish.springbootdemo.pojo.Result;
import com.sailfish.springbootdemo.pojo.db7.*;
import com.sailfish.springbootdemo.utils.ExcelUtils;
import com.sailfish.springbootdemo.utils.ResultUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class TbDynamicVplDataServiceImpl implements TbDynamicVplDataService {
    @Autowired
    private TbUploadVplMetaDao metaDao;
    @Autowired
    private TbDynamicVplDataDao dataDao;
    @Autowired
    private TbVplColumnMetaDao columnDao;
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result handleUpload(MultipartFile file, String fileName) {
        try{

            List<LinkedHashMap<String, String>> rows = ExcelUtils.parseExcelSheet0(file);
            if (rows.isEmpty()) {
                return ResultUtil.error(500, "空的vpl表", "vpl excel is null", null);
            }
            String operatorId = UserHeaderHolder.getUserId();

            // 保存元数据
            UploadVplMeta meta = new UploadVplMeta();
            meta.setFileName(fileName);
            meta.setOperatorId(Integer.parseInt(operatorId));
            meta.setUploadTime(new Date());
            metaDao.insert(meta);
            int metaId = meta.getId();

            // 保存列元数据
            Set<String> columns = rows.get(0).keySet();
            List<VplColumnMeta> columnMetas = columns.stream()
                    .map(col -> new VplColumnMeta(null, metaId, col, inferDataType(rows, col)))
                    .collect(Collectors.toList());
            columnDao.batchInsert(metaId, columnMetas);

            // 获取列ID映射
            Map<String, Integer> columnIdMap = columnDao.selectByMetaId(metaId).stream()
                    .collect(Collectors.toMap(VplColumnMeta::getColumnName, VplColumnMeta::getId));

            // 转换并保存纵表数据
            List<DynamicVplData> dataList = new ArrayList<>();
            for (int rowIdx = 0; rowIdx < rows.size(); rowIdx++) {
                Map<String, String> row = rows.get(rowIdx);
                for (Map.Entry<String, String> entry : row.entrySet()) {
                    DynamicVplData data = new DynamicVplData();
                    data.setMetaId(metaId);
                    data.setRowIndex(rowIdx + 1); // Excel行号从1开始
                    data.setColumnId(columnIdMap.get(entry.getKey()));
                    data.setCellValue(entry.getValue());
                    dataList.add(data);
                }
            }
            dataDao.batchInsert(dataList);
            return ResultUtil.success("vpl表处理成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    // 查询数据重组为横表
    @Override
    public List<Map<String, String>> queryData(int metaId) {
        List<VplColumnMeta> columns = columnDao.selectByMetaId(metaId);
        Map<Integer, String> columnIdToName = columns.stream()
                .collect(Collectors.toMap(VplColumnMeta::getId, VplColumnMeta::getColumnName));

        int maxRow = dataDao.getMaxRowIndex(metaId);
        List<Map<String, String>> result = new ArrayList<>();

        for (int rowIdx = 1; rowIdx <= maxRow; rowIdx++) {
            List<DynamicVplData> rowData = dataDao.selectByRow(metaId, rowIdx);
            Map<String, String> rowMap = new HashMap<>();
            for (DynamicVplData data : rowData) {
                String colName = columnIdToName.get(data.getColumnId());
                rowMap.put(colName, data.getCellValue());
            }
            result.add(rowMap);
        }
        return result;
    }

    // 数据类型推断（示例）
    private String inferDataType(List<LinkedHashMap<String, String>> rows, String columnName) {
        if (rows.isEmpty()) {
            return "STRING"; // 默认返回 STRING 类型
        }

        boolean containsNumber = false;
        boolean containsDate = false;
        boolean containsBoolean = false;

        for (LinkedHashMap<String, String> row : rows) {
            String value = row.get(columnName);

            if (value == null || value.trim().isEmpty()) {
                continue; // 跳过空值
            }

            // 尝试解析为数字
            try {
                Double.parseDouble(value);
                containsNumber = true;
            } catch (NumberFormatException e) {
                containsNumber = false;
            }

            // 尝试解析为日期
            try {
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                dateFormat.setLenient(false);
                dateFormat.parse(value);
                containsDate = true;
            } catch (ParseException e) {
                containsDate = false;
            }

            // 尝试解析为布尔值
            if ("true".equalsIgnoreCase(value) || "false".equalsIgnoreCase(value)) {
                containsBoolean = true;
            }

            // 如果一个字段同时包含不同类型的数据，优先级顺序：DATE > NUMBER > BOOLEAN > STRING
            if (containsDate) {
                return "DATE";
            } else if (containsNumber) {
                return "NUMBER";
            } else if (containsBoolean) {
                return "BOOLEAN";
            }
        }

        return "STRING"; // 默认返回 STRING 类型
    }
}